9. Managing Data Files

Opening and Selecting Database Files
Default File Paths
Navigating in the Database

Opening and Selecting Database Files

To open a database file, call the FileFlex function DBUse, supplying a database file name (including full path name if required) as an argument. Put the result into a variable, because you will need to refer to this file by its returned value later. Be sure not to use the same variable for any two files you to have open at one time.

Just type the following line into your script, changing the name of the database file appropriately.

  put DBUse("HD:DB Files:Test.DBF") into databaseID

Alternatively, you can split the path specification into two parameters: the first the actual file name and the second the path specification. In the runtime, the path specification will remain unchanged but the database file name will be decrypted:

  put DBUse("Test.DBF","HD:DB Files:") into dbID -- Developer FF
  put DBUse("&#R*$*@U","HD:DB Files:") into dbID -- Runtime FF
  put DBUse("Test.DBF","C:\DB\DBFiles\") into dbID -- Windows

Only one database file can be the current file. You may have numerous database files open at once, but only one of them will be the current database at any one time. The DBSelect function will allow you to choose any open database to act as the current database. To choose a previously opened database as the current file, use the FileFlex function DBSelect. It requires a single argument, the variable into which you put the ID of the database when you opened it with your call to DBUse.

  put DBSelect(DatabaseID) into dbResult

Just as there can only be one current database file, so there can only be one record in that file that is recognized by FileFlex as the "current"record. All operations are performed on or relative to this record. When FileFlex switches between databases, the current record is saved.

There are times when you might want to know the database ID of the currently selected database. For example, you may want to interrupt processing on a file to undertake some special processing on another file and then return to processing the original file. To do so, use the FileFlex function DBCurrDBNum. It returns the ID of the currently selected database. This ID is the same as that returned when you called DBUse for this database.

  put DBCurrDBNum()

When you are finished with a database, you can close the file and reclaim the memory used for its buffers by calling the FileFlex function DBClose and passing the database ID returned by the call to DBUse as an argument. DBClose returns an error code of 0 on successful completion, or an alternative error code if there is a problem.

 put DBClose(DatabaseID) into dbResult

If you have more than one file open and want to close a specific open file, you must first insure that the file you want to close is the current file. Use DBSelect for this purpose. If you have more than one database file open and you are ready to end your work with FileFlex (or with that set of files), you can close them all in one step with the DBCloseAll function. It takes no argument.

 put DBCloseAll() into dbResult

Default File Paths

When using FileFlex, it's often easy to confuse the default working directory with the directory where your files are located. Here's the typical manifestation of the problem:

"I've been trying to open the sample video database file included with FileFlex. When I try opening the file, FileFlex returns a -120 error. What's happening?"

Confusion is understandable. Imagine you've got a script of this form:

  on startMovie
    put DBOpenSession() into dbResult 
    put DBUse("Video") into videoID
  end startMovie

First off, you should always check the results of DBUse. In this case, DBUse will return an error unless Video.DBF is located in the same folder as your Director application. While it might seem logical that Director and FileFlex would look in the directory where your movie is located, the concept of a current working directory is based on the folder where the application (in this case Director) resides.

To fix the problem, either move your database files to the same folder as Director, or prepend the complete path name (i.e., "Windy City:FileFlex Projects:Video Project:") before the name "Video.DBF". Obviously, Windows users will use backslash (\) instead of colon (:).

Navigating in the Database

To count the number of records in the current database file, you can use the FileFlex DBCount function. Since it operates only on the current database, you must first call the DBSelect function to make the desired database current if it is not already. The first line below makes the selected database current. The second line will then return the number of records in the database file.

  put DBSelect(databaseID) into dbResult
  put DBCount() into numRecs

To find the record number of the current record in the current database file, you can use the FileFlex DBCurrRecNum function. This routine will return the current record number. This routine returns the physical record number. If the logical record order has been changed with DBQuery or indexing, moving one record forward or backward in the database won't correspond directly to adding one or subtracting one from the physical record number.

  put DBCurrRecNum() into CurrentRecordNumber

You can position the current record pointer at any physical record, even with a currently active index, by using the FileFlex DBGo function. It takes one argument, the record number to which you wish to be positioned. It does not retrieve any data.

  put DBGo(39) into dbResult

The above line will move the current record pointer to physical record 39. A "physical" record is usually different from a "logical"record, which refers to the record's number in indexed, or sorted, sequence.

You may sometimes want to get to the top of the database file or to the end of the file to find out the last record number so you can add a new record. FileFlex supplies two functions for these purposes.

To get to the top of the current database file and retrieve the value of the first record in the file, use the DBTop function. This will position the current record pointer to the top record in the database (physical record or, if an index is open, the first record in index order). You can then use DBGetCurrRecVal to retrieve the data in this record into fields or a container as desired.

Type the following lines, changing the name of the container as appropriate, to have FileFlex move to the first record in the current database and put that record's field values into a named container:

  put DBTop() into dbResult
  put DBGetCurrRecVal("A") into contents

If you don't know the number of the record you want to access, but you know its position relative to the current one, use the DBSkip function. You can move the current database record pointer forward or backward a specified number of records with the FileFlex DBSkip function. You must supply an integer (positive or negative) describing the number of records to skip. A negative number tells FileFlex to move backward in the file. When it reaches the indicated record, FileFlex leaves the record pointer positioned at the record. You can then use the DBGetCurrRecVal function to retrieve the fields in that record as desired.

  put DBSkip(23) into skipResult
  put DBGetCurrRecVal("A") into dbResult

The behavior of DBSkip depends on whether there is an index file open or whether a DBQuery operation is currently active. If an index file is currently in use, DBSkip will skip the number of records in the sequence in which the index sorts the file. If a DBQuery function has been called with an argument other than an empty string, then DBSkip will skip records matching the search criteria defined in the DBQuery function.

  [Previous Chapter]    [Table of Contents]    [Next Chapter]